﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Configuration;
using MySql.Data.MySqlClient;
using System.Data.SqlClient;

namespace CSP.DataAccess
{
    public class SqlHelperFactory
    {
        /// <summary>
        /// 获取mysql 配置文件的key
        /// </summary>
        private const string CONNKEY = "CSPContext";

        /// <summary>
        /// 获取连接字符串 
        /// </summary>
        /// <param name="connKey">指定配置名称</param>
        /// <returns></returns>
        private static string GetConnectionString(string connKey)
        {
            return ConfigurationManager.ConnectionStrings[connKey].ToString();
        }

        /// <summary>
        /// 获取CSP.BBS连接字符串
        /// </summary>
        /// <returns></returns>
        private static string GetBBSConnectionString()
        {
            return GetConnectionString(CONNKEY);
        }

        /// <summary>
        /// 获取Connection 实例
        /// </summary>
        /// <param name="connString"></param>
        /// <returns></returns>
        private static SqlConnection GetConnection(string connString)
        {
            return new SqlConnection(connString);
        }

        /// <summary>
        /// 获取bbs连接
        /// </summary>
        /// <returns></returns>
        public static SqlConnection GetBBSConnection()
        {
            return GetConnection(GetBBSConnectionString());
        }


        /// <summary>
        /// 执行指定数据库连接字符串的命令,返回DataSet.
        /// </summary>
        /// <remarks>
        /// 示例: 
        ///  DataSet ds = ExecuteDataset( CommandType.StoredProcedure, "GetOrders");
        /// </remarks>
        /// <param>命令类型 (存储过程,命令文本或其它)</param>
        /// <param>命令类型 (存储过程,命令文本或其它)</param>
        /// <param>存储过程名称或T-SQL语句</param>
        /// <returns>返回一个包含结果集的DataSet</returns>
        public static DataSet ExecuteDataset(SqlConnection SqlConnection, CommandType commandType, string commandText)
        {
            return SqlHelper.ExecuteDataset(SqlConnection, commandType, commandText);
        }

        /// <summary>
        /// 执行指定数据库连接对象的命令,指定存储过程参数,返回DataSet.
        /// </summary>
        /// <remarks>
        /// 示例: 
        ///  DataSet ds = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param>一个有效的数据库连接对象</param>
        /// <param>命令类型 (存储过程,命令文本或其它)</param>
        /// <param>存储过程名或T-SQL语句</param>
        /// <param>SqlParamter参数数组</param>
        /// <returns>返回一个包含结果集的DataSet</returns>
        public static DataSet ExecuteDataset(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
            return SqlHelper.ExecuteDataset(connection, commandType, commandText, commandParameters);
        }

        /// <summary>
        /// 执行指定数据库连接对象的命令,指定参数值,返回DataSet.
        /// </summary>
        /// <remarks>
        /// 此方法不提供访问存储过程输入参数和返回值.
        /// 示例.: 
        ///  DataSet ds = ExecuteDataset(conn, "GetOrders", 24, 36);
        /// </remarks>
        /// <param>一个有效的数据库连接对象</param>
        /// <param>存储过程名</param>
        /// <param>分配给存储过程输入参数的对象数组</param>
        /// <returns>返回一个包含结果集的DataSet</returns>
        public static DataSet ExecuteDataset(SqlConnection connection, string spName, params object[] parameterValues)
        {
            return SqlHelper.ExecuteDataset(connection, spName, parameterValues);
        }

        /// <summary>
        /// 执行指定事务的命令,返回DataSet.
        /// </summary>
        /// <remarks>
        /// 示例: 
        ///  DataSet ds = ExecuteDataset(trans, CommandType.StoredProcedure, "GetOrders");
        /// </remarks>
        /// <param>事务</param>
        /// <param>命令类型 (存储过程,命令文本或其它)</param>
        /// <param>存储过程名或T-SQL语句</param>
        /// <returns>返回一个包含结果集的DataSet</returns>
        public static DataSet ExecuteDataset(SqlTransaction transaction, CommandType commandType, string commandText)
        {
            return SqlHelper.ExecuteDataset(transaction, commandType, commandText);
        }

        /// <summary>
        /// 执行指定事务的命令,指定参数,返回DataSet.
        /// </summary>
        /// <remarks>
        /// 示例: 
        ///  DataSet ds = ExecuteDataset(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param>事务</param>
        /// <param>命令类型 (存储过程,命令文本或其它)</param>
        /// <param>存储过程名或T-SQL语句</param>
        /// <param>SqlParamter参数数组</param>
        /// <returns>返回一个包含结果集的DataSet</returns>
        public static DataSet ExecuteDataset(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
            return SqlHelper.ExecuteDataset(transaction, commandType, commandText, commandParameters);
        }

        /// <summary>
        /// 执行指定数据库连接字符串的数据阅读器.
        /// </summary>
        /// <remarks>
        /// 示例: 
        ///  SqlDataReader dr = ExecuteReader(connString, CommandType.StoredProcedure, "GetOrders");
        /// </remarks>
        /// <param>一个有效的数据库连接字符串</param>
        /// <param>命令类型 (存储过程,命令文本或其它)</param>
        /// <param>存储过程名或T-SQL语句</param>
        /// <returns>返回包含结果集的SqlDataReader</returns>
        public static SqlDataReader ExecuteReader(SqlConnection connection, CommandType commandType, string commandText)
        {
            return SqlHelper.ExecuteReader(connection, commandType, commandText);
        }

        /// <summary>
        /// 执行指定数据库连接字符串的数据阅读器,指定参数.
        /// </summary>
        /// <remarks>
        /// 示例: 
        ///  SqlDataReader dr = ExecuteReader(connString, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param>一个有效的数据库连接字符串</param>
        /// <param>命令类型 (存储过程,命令文本或其它)</param>
        /// <param>存储过程名或T-SQL语句</param>
        /// <param>SqlParamter参数数组(new SqlParameter("@prodid", 24))</param>
        /// <returns>返回包含结果集的SqlDataReader</returns>
        public static SqlDataReader ExecuteReader(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
            return SqlHelper.ExecuteReader(connection, commandType, commandText, commandParameters);
        }

        /// <summary>
        /// 执行指定数据库连接字符串的数据阅读器,指定参数值.
        /// </summary>
        /// <remarks>
        /// 此方法不提供访问存储过程输出参数和返回值参数.
        /// 示例: 
        ///  SqlDataReader dr = ExecuteReader(connString, "GetOrders", 24, 36);
        /// </remarks>
        /// <param>一个有效的数据库连接字符串</param>
        /// <param>存储过程名</param>
        /// <param>分配给存储过程输入参数的对象数组</param>
        /// <returns>返回包含结果集的SqlDataReader</returns>
        public static SqlDataReader ExecuteReader(SqlConnection connection, string spName, params object[] parameterValues)
        {
            return SqlHelper.ExecuteReader(connection, spName, parameterValues);
        }

        /// <summary>
        /// [调用者方式]执行指定数据库事务的数据阅读器,指定参数值.
        /// </summary>
        /// <remarks>
        /// 示例: 
        ///  SqlDataReader dr = ExecuteReader(trans, CommandType.StoredProcedure, "GetOrders");
        /// </remarks>
        /// <param>一个有效的连接事务</param>
        /// <param>命令类型 (存储过程,命令文本或其它)</param>
        /// <param>存储过程名称或T-SQL语句</param>
        /// <returns>返回包含结果集的SqlDataReader</returns>
        public static SqlDataReader ExecuteReader(SqlTransaction transaction, CommandType commandType, string commandText)
        {
            return SqlHelper.ExecuteReader(transaction, commandType, commandText);
        }

        /// <summary>
        /// [调用者方式]执行指定数据库事务的数据阅读器,指定参数.
        /// </summary>
        /// <remarks>
        /// 示例: 
        ///   SqlDataReader dr = ExecuteReader(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param>一个有效的连接事务</param>
        /// <param>命令类型 (存储过程,命令文本或其它)</param>
        /// <param>存储过程名称或T-SQL语句</param>
        /// <param>分配给命令的SqlParamter参数数组</param>
        /// <returns>返回包含结果集的SqlDataReader</returns>
        public static SqlDataReader ExecuteReader(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
            return SqlHelper.ExecuteReader(transaction, commandType, commandText, commandParameters);
        }


        /// <summary>
        /// [调用者方式]执行指定数据库事务的数据阅读器,指定参数值.
        /// </summary>
        /// <remarks>
        /// 此方法不提供访问存储过程输出参数和返回值参数.
        ///
        /// 示例: 
        ///  SqlDataReader dr = ExecuteReader(trans, "GetOrders", 24, 36);
        /// </remarks>
        /// <param>一个有效的连接事务</param>
        /// <param>存储过程名称</param>
        /// <param>分配给存储过程输入参数的对象数组</param>
        /// <returns>返回包含结果集的SqlDataReader</returns>
        public static SqlDataReader ExecuteReader(SqlTransaction transaction, string spName, params object[] parameterValues)
        {
            return SqlHelper.ExecuteReader(transaction, spName, parameterValues);
        }

        /// <summary>
        /// 执行指定数据库连接字符串的命令,返回结果集中的第一行第一列.
        /// </summary>
        /// <remarks>
        /// 示例: 
        ///  int orderCount = (int)ExecuteScalar(connString, CommandType.StoredProcedure, "GetOrderCount");
        /// </remarks>
        /// <param>一个有效的数据库连接字符串</param>
        /// <param>命令类型 (存储过程,命令文本或其它)</param>
        /// <param>存储过程名称或T-SQL语句</param>
        /// <returns>返回结果集中的第一行第一列</returns>
        public static object ExecuteScalar(SqlConnection connection, CommandType commandType, string commandText)
        {
            return SqlHelper.ExecuteScalar(connection, commandType, commandText);
        }

        /// <summary>
        /// 执行指定连接字符串,类型的MySqlCommand.
        /// </summary>
        /// <remarks>
        /// 示例: 
        ///  int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders");
        /// </remarks>
        /// <param>一个有效的数据库连接字符串</param>
        /// <param>命令类型 (存储过程,命令文本, 其它.)</param>
        /// <param>存储过程名称或SQL语句</param>
        /// <returns>返回命令影响的行数</returns>
        public static int ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText)
        {
            return SqlHelper.ExecuteNonQuery(connection, commandType, commandText);
        }

        /// <summary>
        /// 执行指定数据库连接对象的命令
        /// </summary>
        /// <remarks>
        /// 示例: 
        ///  int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param>一个有效的数据库连接对象</param>
        /// <param>命令类型(存储过程,命令文本或其它.)</param>
        /// <param>T存储过程名称或T-SQL语句</param>
        /// <param>SqlParamter参数数组</param>
        /// <returns>返回影响的行数</returns>
        public static int ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] parameterValues)
        {
            return SqlHelper.ExecuteNonQuery(connection, commandType, commandText, parameterValues);
        }
    }
}
